﻿use master
go
drop database qldttc
go
create database qldttc
go
use qldttc
go
create table account
(
id int primary key identity,
userName varchar(30) not null unique,
pass varchar(20) not null,
quyenHan varchar(30)
)
go
create table monHoc(
	maMH varchar(55) primary key,
	tenMH nvarchar(55) not null,
	soTC int not null,
	HSChuyenCan float not null,
	HSGiuaky float not null,
	HSThiKetthuc float not null
)
go
create table khoa(
	makhoa varchar(55) primary key,
	tenkhoa nvarchar(55) not null
)
go
create table nienKhoa(
	maNK varchar(55) primary key,
	tenNK varchar(55) not null
)


go
create table giaoVien(
	maGV varchar(55) primary key,
	tenGV nvarchar(50) not null,
	ngaySinh Date not null,
	gioiTinh nvarchar(11) not null,
	queQuan nvarchar(55) not null,
	phone varchar(22),
	email varchar(44),
	SCMND varchar(22) not null unique
)
go
create table lopHP(
	maLopHP varchar(55) primary key,
	tenLopHP nvarchar(55) not null,	
	makhoa varchar(55) not null references khoa(makhoa),
	maNK varchar(55) not null references nienKhoa(maNK),
	maMH varchar(55) not null references monHoc(maMH),
	maGV varchar(55) not null references giaoVien(maGV),
	ngayBatdau Datetime not null,
	ngayKetthuc Datetime not null,
	siSo int not null,---so hoc sinh toi da cua lop
	soHSDK int default 0	--so hoc sinh dang ki
)
go
create table lopSH(
	malopSH varchar(55) primary key,
	tenlopSH nvarchar(55) not null,
	makhoa varchar(55) not null references khoa(makhoa),
	maNK varchar(55) not null references nienKhoa(maNK)	
)
go
create table sinhVien(
	maSV varchar(55) primary key,
	malopSH varchar(55) not null references lopSH(malopSH),
	hoTen nvarchar(55) not null,
	ngaySinh Date not null,
	gioiTinh nvarchar(11) not null,
	queQuan nvarchar(55) not null,
	phone varchar(22),
	email varchar(44),
	SCMND varchar(22) not null unique,
	doiTuong nvarchar(50) not null,
	ghiChu nvarchar(55)
)
go
create table lopHP_SV(
	Id int identity primary key,
	maLopHP varchar(55) not null references lopHP(maLopHP),
	maSV varchar(55) not null references sinhVien(maSV)	
)
go

create table giaoVien_MH(
	Id int identity primary key,
	maGV varchar(55) references giaoVien(maGV),
	maMH varchar(55) references monHoc(maMH)
)
go
create table giaoVien_lopHP(
	Id int primary key identity,
	maGV varchar(55) references giaoVien(maGV),
	maLopHP varchar(55) references lopHP(maLopHP)
)
go
create table ketQua(
	maMH varchar(55) not null references monHoc(maMH),
	maSV varchar(55)  not null references sinhVien(maSV),
	hocKy int,	
	DChuyencan float,	
	DGiuaky float,
	DThiKetthuc float,		
	DTrungBinh float,
	xepLoai varchar(5),
	DTichLuy float,
	ghiChu varchar(50),
	Constraint PK  primary key(maMH, maSV)
	)
go
create table khoa_MH(
	Id int primary key identity,
	makhoa varchar(55) references khoa(makhoa),
	maMH varchar(55) references monHoc(maMH)	 
)
go
create table phongBan
(
	maPB varchar(20) primary key,
	tenPb varchar(50) not null,
)
go
create table trangThaiChucnang(
	id int identity primary key,
	dangkiHP int not null,
	huyHP int not null
)
go
create table thongBaoSinhVien(
	id int identity primary key,
	maSV varchar(55) references sinhVien(maSV),
	noidung nvarchar(500)
)
go
create table thongBaoGiaovien(
	id int identity primary key,
	maGV varchar(55) references giaoVien(maGV),
	noidung nvarchar(500)
)
go
--tao thu tuc
--proc tinh diem trung binh
create proc tinhDTB
(
	@DChuyencan Decimal(18,1),	
	@DGiuaky float,
	@DThiKetthuc float
)as
	declare @DTrungBinh float
	set @DTrungBinh = @DChuyencan*0.2+@DGiuaky*0.2+@DThiKetthuc*0.6
go
--tao trigger
---trigger tu tang so sinh vien dang ki khi insert HP o bang LopHp_SV
Create trigger Insert_lopHP_SV
on lopHP_SV
for insert
as 
	update lopHP
	set lopHP.soHSDK=lopHP.soHSDK+1
	from lopHP inner join inserted
					on lopHP.maLopHP=inserted.maLopHP
go
---trigger tu giam so sinh vien dang ki khi xoa HP o bang LopHp_SV
Create trigger Delete_lopHP_SV
on lopHP_SV
for delete
as
	update lopHP
	set lopHP.soHSDK=lopHP.soHSDK-1
	from LopHP inner join deleted
					on lopHP.maLopHP=deleted.maLopHP
go
---trigger tự tạo tài khoản khi thêm mới một sinh viên
create trigger insert_sinhVien
on sinhVien
for insert
as
begin
	DECLARE @maSV varchar(55)
	select @maSV = maSV from inserted					
	insert into account values(@maSV,@maSV,'sinhvien')
end		
go
---triger tự xóa tài khoản khi xóa sinh viên
create trigger delete_sinhVien
on sinhVien
for delete
as
begin
	delete from account where userName=(select maSV from deleted)	
end		
go
---trigger tự tạo tài khoản khi thêm mới một giáo viên
create trigger insert_giaoVien
on giaoVien
for insert
as
begin
	DECLARE @maGV varchar(55)
	select @maGV = maGV from inserted					
	insert into account values(@maGV,@maGV,'giaovien')
end		
go
---triger tự xóa tài khoản khi xóa giáo viên
create trigger delete_giaoVien
on giaoVien
for delete
as
begin
	delete from account where userName=(select maGV from deleted)	
end		
go
--trigger tu dong cap nhap mot bang diem khi sinh vien dang ki vao 1 lop HP
create trigger insert_lopHP_SV_Diem
on lopHP_SV
for insert
as
begin
	declare @maSV varchar(55),@maMH varchar(55)
	select @maSV = maSV from inserted
	select @maMH = maMH from inserted,lopHP where inserted.maLopHP=lopHP.maLopHP
	insert into ketQua values(@maMH,@maSV,null,null,null,null,null,null,null,null)
end	
go
--- trigger tu dong them tai khoan khi them moi mot phong ban
create trigger insert_phongBan
on phongBan
for insert
as
begin
	DECLARE @maPB varchar(55)
	select @maPB = maPB from inserted					
	insert into account values(@maPB,@maPB,@maPB)
end		
go
--- trigger tu dong xoa tai khoan khi xoa mot phong ban
go
create trigger delete_phongBan
on phongBan
for insert
as
begin
	delete from account where userName=(select maPB from deleted)	
end	
go

--insert data
go
insert into monHoc values ('PTTKHT',N'Phân tích thiết kế hệ thống',3,2,2,6);
insert into monHoc values ('ATBM',N'An toàn bảo mật',3,2,2,6);
insert into monHoc values ('TAT',N'Tiếng anh Toeic',3,2,2,6);
insert into monHoc values ('DHMT',N'Đồ họa máy tính',3,2,2,6);
insert into monHoc values ('PPT',N'Phương pháp tính',3,2,2,6);
insert into monHoc values ('LTW',N'Lập trình Window',3,2,2,6);
insert into monHoc values ('HDT',N'Hướng đối tượng',3,2,2,6);
go
select * from monHoc
go
insert into khoa values('CNTT',N'Công nghệ thông tin');
insert into khoa values('KT',N'Kế toán');
insert into khoa values('QTKD',N'Quản trị kinh doanh');
insert into khoa values('QTKSDL',N'Quản trị khách sạn và du lịch');
insert into khoa values('DT',N'khoa điện tử');
go
select * from khoa
go
insert into nienKhoa values('2000','2000-2004');
insert into nienKhoa values('2001','2001-2005');
insert into nienKhoa values('2002','2002-2006');
insert into nienKhoa values('2003','2003-2007');
insert into nienKhoa values('2004','2004-2008');
insert into nienKhoa values('2005','2005-2009');
insert into nienKhoa values('2006','2006-2010');
insert into nienKhoa values('2007','2007-2011');
insert into nienKhoa values('2008','2008-2012');
insert into nienKhoa values('2009','2009-2013');
insert into nienKhoa values('2010','2010-2014');
insert into nienKhoa values('2011','2011-2015');
insert into nienKhoa values('2012','2012-2016');
go
select * from nienKhoa
go
insert into lopSH values('KTPM2-K1',N'Kĩ thuật phần mềm 2','CNTT','2010');
insert into lopSH values('KTPM1-K1',N'Kĩ thuật phần mềm 1','CNTT','2010');
insert into lopSH values('KHMT1-K1',N'khoa học máy tính 1','CNTT','2010');
insert into lopSH values('QTDLKS1-K1',N'Quản trị du lịch khách sạn 1','QTKSDL','2010');
insert into lopSH values('QTDLKS2-K1',N'Quản trị du lịch khách sạn 2','QTKSDL','2010');
insert into lopSH values('QTDLKS3-K1',N'Quản trị du lịch khách sạn 3','QTKSDL','2010');
insert into lopSH values('KT1-K1',N'Kế toán','KT','2010');
insert into lopSH values('KTPM2-K2',N'Kĩ thuật phần mềm 2','CNTT','2011');
insert into lopSH values('KTPM1-K2',N'Kĩ thuật phần mềm 1','CNTT','2011');
insert into lopSH values('KHMT1-K2',N'khoa học máy tính 1','CNTT','2011');
go
select * from lopSH
go
insert into account values('admin','admin','admin')

go
insert into giaoVien values('GV001',N'Nguyễn Văn Hòa','08/08/1980',N'Nam',N'Hà Nội','0984919330','Hoa@yahoo.com','123456789')
insert into giaoVien values('GV002',N'Nguyễn Văn Tuấn','08/08/1980',N'Nam',N'Hà Nội','0983919330','Tuan@yahoo.com','123456782')
insert into giaoVien values('GV003',N'Nguyễn Văn Hoàn','08/08/1980',N'Nam',N'Hà Nội','0982919330','Hoan@yahoo.com','123456729')
go
select * from giaoVien
insert into Sinhvien values('0900100','KTPM2-K1',N'Nguyễn Thị Phương Anh','08/28/1991',N'Nữ',N'Hà Nội','01656102398','anhphuongict@gmail.com','012855162',N'Sinh viên',null);
insert into Sinhvien values('0901282','KTPM2-K1',N'Pham Văn Thặng','10/20/1990',N'Nam',N'Hải Dương','0972899975','thang_pv_90@gmail.com','123456782',N'Sinh viên',null);
insert into Sinhvien values('0900571','KTPM2-K1',N'Đặng Thị Thu Thuỷ','08/07/1991',N'Nữ',N'Hải Phòng','0989999999','thuydhtin1@gmail.com','123456723',N'Sinh viên',null);
insert into Sinhvien values('0900137','KTPM2-K1',N'Nguyễn Thị Ly','08/25/1990',N'Nữ',N'Hà Nội','0989999999','lynt@gmail.com','123456734',N'Sinh viên',null);
go
select * from Sinhvien
go
insert into phongBan values('PDT',N'Phòng Đào tạo')
insert into phongBan values('PQLSV',N'Phòng QLSV')
insert into phongBan values('PQLGV',N'Phòng QLGV')
go
insert into TrangThaiChucnang values(1,1)
go
select * from account
select * from ketQua
select * from lopHP_SV
select lopHP.tenLopHP, sinhVien.hoTen, monHoc.tenMH, lopHP.ngayBatdau, lopHP.ngayKetthuc, giaoVien.tenGV from lopHP, sinhVien, monHoc, giaoVien, lopHP_SV
where sinhVien.maSV = lopHP_SV.maSV and lopHP.maLopHP = lopHP_SV.maLopHP and lopHP.maMH = monHoc.maMH and lopHP.maGV = giaoVien.maGV
go